﻿using System;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;

namespace Utility
{
    public class ExcelCopyTemplate
    {
        public static ExcelCopyTemplate Instance = new ExcelCopyTemplate();

        private ExcelCopyTemplate() { }

        private HSSFWorkbook srcWorkbook;
        private HSSFWorkbook resWorkbook;

        /// <summary>
        /// 初始化工作簿
        /// </summary>
        /// <param name="path"></param>
        public HSSFWorkbook InitWorkbook()
        {
            srcWorkbook = new HSSFWorkbook();

            return srcWorkbook;
        }

        private HSSFSheet InitSheet(HSSFWorkbook workbook, string sheetName)
        {
            return workbook.CreateSheet(sheetName);
        }

        private HSSFSheet GetExcelSheet(string path)
        {
            try
            {
                resWorkbook = new HSSFWorkbook(File.OpenRead(path));

                return resWorkbook.GetSheetAt(0);
            }
            catch
            {
                return null;
            }
        }

        private int CopySheet(HSSFSheet srcSheet, HSSFSheet resSheet, int lastRow)
        {
            try
            {
                HSSFRow srcRow = null;
                HSSFRow resRow = null;
                HSSFCell srcCell = null;
                HSSFCell resCell = null;
                srcSheet.PrintSetup.PaperSize = 9;
                srcSheet.PrintSetup.FooterMargin = 0.8;
                srcSheet.PrintSetup.HeaderMargin = 0.8;
                srcSheet.PrintSetup.Landscape = true;
                srcSheet.SetMargin(HSSFSheet.TopMargin, (double)0.9);
                srcSheet.SetMargin(HSSFSheet.BottomMargin, (double)0.9);
                srcSheet.SetMargin(HSSFSheet.LeftMargin, (double)0.8);
                srcSheet.SetMargin(HSSFSheet.RightMargin, (double)0.8);

                for (int i = lastRow; i <= resSheet.LastRowNum + lastRow; ++i)
                {
                    srcRow = srcSheet.CreateRow(i);
                    resRow = resSheet.GetRow(i - lastRow);

                    if (resRow.RowStyle != null)
                    {
                        srcRow.RowStyle = srcWorkbook.CreateCellStyle();
                        srcRow.RowStyle.CloneStyleFrom(resRow.RowStyle);
                    }

                    srcRow.Height = resRow.Height;

                    for (int j = 0; j < resRow.LastCellNum; ++j)
                    {
                        srcCell = srcRow.CreateCell(j);
                        resCell = resRow.GetCell(j);
                        srcSheet.SetColumnWidth(j, resSheet.GetColumnWidth(j));

                        if (resCell != null)
                        {
                            switch (resCell.CellType)
                            {
                                case HSSFCell.CELL_TYPE_NUMERIC:
                                    srcCell.SetCellValue(resCell.NumericCellValue.ToString());
                                    break;
                                case HSSFCell.CELL_TYPE_STRING:
                                    srcCell.SetCellValue(resCell.StringCellValue);
                                    break;
                            }

                            if (resCell.CellStyle != null)
                            {
                                srcCell.CellStyle = srcWorkbook.CreateCellStyle();
                                srcCell.CellStyle.CloneStyleFrom(resCell.CellStyle);
                                srcCell.CellStyle.SetFont(resCell.CellStyle.GetFont(resWorkbook));
                            }
                        }
                    }
                }

                for (int i = 0; i < resSheet.NumMergedRegions; ++i)
                {
                    Region region = resSheet.GetMergedRegionAt(i);
                    if (region != null)
                    {
                        region.RowFrom += lastRow;
                        region.RowTo += lastRow;
                        srcSheet.AddMergedRegion(region);
                    }
                }

                return resSheet.LastRowNum + lastRow;
            }
            catch
            {
                return -1;
            }
        }

        private bool AddRowData(HSSFSheet sheet, DataRow dr, int row)
        {
            try
            {
                for (int col = 0; col < dr.ItemArray.Length; ++col)
                {
                    sheet.GetRow(row).GetCell(col + 1).SetCellValue(dr.ItemArray.GetValue(col).ToString());
                }

                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool ExprotToExcel(DataTable dt, string path, string templatePath, string sheetName, int maxRow)
        {
            try
            {
                HSSFWorkbook workbook = InitWorkbook();

                HSSFSheet srcSheet = InitSheet(workbook, sheetName);

                HSSFSheet resSheet = GetExcelSheet(templatePath);
                int lastRow = 0;
                int count = 0;

                for (int i = 0; i < dt.Rows.Count; ++i)
                {
                    if (i % 20 == 0)
                    {
                        lastRow = CopySheet(srcSheet, resSheet, lastRow + (count > 0 ? 1 : 0));
                        count++;
                    }
                    AddRowData(srcSheet, dt.Rows[i], i % 20 + (count - 1) * maxRow + 3);
                }

                SaveExcel(path);

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        private void SaveExcel(string filePath)
        {
            FileStream file = null;

            filePath = filePath.Replace("\\", "/");
            string path = filePath.Substring(0, filePath.LastIndexOf("/") + 1);

            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);

            file = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite);
            srcWorkbook.Write(file);
            file.Close();
        }
    }
}